#!/usr/bin/env python
# -*- coding:utf-8 -*-
import MySQLdb

def sql_init():
    conn=MySQLdb.connect(host='192.168.136.8',user='root',passwd='123456',db='HostManager')
    cur = conn.cursor()
    # cur.execute('CREATE DATABASE HostManager;'  #if not db:创建主机管理的数据库HostManager)
    try:
        cur.execute(
            "CREATE TABLE user (\
            user_id INT NOT NULL AUTO_INCREMENT , \
            user_name CHAR(10) NOT NULL ,\
            PRIMARY KEY (user_id)\
            );"
        )
    except Exception,e:
        print e[1]
    finally:
        user_list=["root","mac","tom"]
        cur.executemany("INSERT INTO user(user_name) values(%s) ",user_list)
    try:
        cur.execute(
            "CREATE TABLE host (\
            host_id INT NOT NULL AUTO_INCREMENT ,\
            hostname CHAR(20) ,\
            host_ip CHAR(40) NOT NULL ,\
            user_id INT NOT NULL , \
            PRIMARY KEY (host_id)\
            );"
        )
    except Exception,e:
        print e[1]
    finally:
        ip_list=[
            ('host11','192.168.136.11',1),
            ('host22','192.168.136.22',1),
            ('host11','192.168.136.11',2),
            ('host22','192.168.136.22',3)
        ]
        cur.executemany('INSERT INTO host(hostname,host_ip,user_id) VALUES (%s,%s,%s)',ip_list)

    conn.commit()
    cur.close()
    conn.close()

def fetch_host(username):
    conn=MySQLdb.connect(host='192.168.136.8',user='root',passwd='123456',db='HostManager')
    cur = conn.cursor()
    cur.execute('SELECT hostname,host_ip FROM host,user WHERE host.user_id=user.user_id and user.user_name=%s',username)
    ret=cur.fetchall()
    cur.close()
    conn.close()
    return ret